/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package facade;

import delta.reporting.PopularProducts;
import entity.Category;
import entity.Product;
import java.util.List;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Expression;
import javax.persistence.criteria.ParameterExpression;
import javax.persistence.criteria.Root;

/**
 *
 * @author Kate, Ivchenko Oleg
 */
@Stateless
public class ProductFacade extends AbstractFacade implements ProductFacadeLocal {

    @PersistenceContext(unitName = "Delta-ejbPU")
    EntityManager em;

    @Override
    protected EntityManager getEntityManager() {
        return em;
    }

    public ProductFacade() {
        super(Product.class);
    }

    public void create(Product entity) {
        super.create(entity);
    }

    public void edit(Product entity) {
        super.edit(entity);
    }

    public void remove(Product entity) {
        entity.setStatus(1);
        em.merge(entity);

    }

    public Product find(int id) {
        return (Product) super.find(id);
    }

    public List<Product> findAll() {
        return super.findAll();
    }

    public List<Product> findRange(int firstResult, int maxResults) {
        return super.findRange(firstResult, maxResults);
    }

    public List<Product> findByName(String name, int firstResult, int maxResults) {

        return getEntityManager()
                .createNamedQuery("Product.findByName", Product.class)
                .setParameter("name", "%" + name + "%")
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();
    }

    public List<Product> findByCategory(Category category, int firstResult, int maxResults) {
        return getEntityManager()
                .createNamedQuery("Product.findByCategory", Product.class)
                .setParameter("category", category)
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();

    }


    public List<Product> findActive(int firstResult, int maxResults) {
        return getEntityManager()
                .createNamedQuery("Product.findActive", Product.class)
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();

    }


    public List<PopularProducts> findPopularProducts(int maxResults) {
        return getEntityManager()
                .createNativeQuery(Product.getPopularProductsQuery(), PopularProducts.class)
                .setParameter(1, maxResults)
                .getResultList();
    }

    /**
     * finds all active products (sorted)
     */
    public List<Product> findActive(int firstResult, int maxResults, String sortBy, boolean isAsc) {
        CriteriaBuilder cb = em.getCriteriaBuilder();

        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);
        query.select(product);
        ParameterExpression<Integer> status = cb.parameter(Integer.class);

        query.where(cb.equal(product.get("status"), status));
        if (isAsc) {
            query.orderBy(cb.asc(product.get(sortBy)));
        } else {
            query.orderBy(cb.desc(product.get(sortBy)));
        }

        return em.createQuery(query).setParameter(status, 0)
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();

    }

    /**
     * finds all active products (sorted) filtered by name
     */
    public List<Product> findActive(int firstResult,
            int maxResults,
            String sortBy,
            boolean isAsc,
            String productName) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);

        ParameterExpression<Integer> status = cb.parameter(Integer.class);
        ParameterExpression<String> name = cb.parameter(String.class);
        Expression<String> namePath = product.get("name");

        query.select(product);
        query.where(cb.equal(product.get("status"), status),
                cb.like(cb.lower(namePath), cb.lower(name),'^'));
        if (isAsc) {
            query.orderBy(cb.asc(product.get(sortBy)));
        } else {
            query.orderBy(cb.desc(product.get(sortBy)));
        }

        return em.createQuery(query).setParameter(status, 0)
                .setParameter(name, "%" + productName.replaceAll("%","^%") + "%")
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();
    }

    /**
     * finds all active products (sorted)filtered by category
     */
    public List<Product> findActive(int firstResult,
            int maxResults,
            String sortBy,
            boolean isAsc,
            Category id_category) {

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);
        query.select(product);

        ParameterExpression<Integer> status = cb.parameter(Integer.class);
        ParameterExpression<Category> category = cb.parameter(Category.class);


        query.where(cb.equal(product.get("status"), status),
                cb.equal(product.get("category"), category));
        if (isAsc) {
            query.orderBy(cb.asc(product.get(sortBy)));
        } else {
            query.orderBy(cb.desc(product.get(sortBy)));
        }

        return em.createQuery(query)
                .setParameter(status, 0)
                .setParameter(category, id_category)
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();

    }

    /**
     * finds all active products (sorted) filtered by name, category
     */
    public List<Product> findActive(int firstResult,
            int maxResults,
            String sortBy,
            boolean isAsc,
            String productName,
            Category id_category) {

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);

        ParameterExpression<Integer> status = cb.parameter(Integer.class);
        ParameterExpression<String> name = cb.parameter(String.class);
        ParameterExpression<Category> category = cb.parameter(Category.class);
        Expression<String> namePath = product.get("name");

        query.select(product);
        query.where(cb.equal(product.get("status"), status),
                cb.like(cb.lower(namePath), cb.lower(name),'%'),
                cb.equal(product.get("category"), category));
        if (isAsc) {
            query.orderBy(cb.asc(product.get(sortBy)));
        } else {
            query.orderBy(cb.desc(product.get(sortBy)));
        }

        return em.createQuery(query)
                .setParameter(status, 0)
                .setParameter(name, "%" + productName.replaceAll("%","^%") + "%")
                .setParameter(category, id_category)
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();

    }

    /**
     * counts active products (sorted)
     */
    public long countActive() {
        long count = (Long) em.createNamedQuery("Product.countActive")
                .getSingleResult();
        return count;
    }

    /**
     * counts active products (sorted) filtered by name
     */
    public long countActiveByName(String name) {
        long count = (Long) em.createNamedQuery("Product.countActiveByName")
                .setParameter("name", "%" + name.replaceAll("%", "^%") + "%")
                .getSingleResult();
        return count;
    }

    /**
     * counts active products (sorted)filtered by category
     */
    public long countActiveByCategory(Category id_category) {
        long count = (Long) em.createNamedQuery("Product.countActiveByCategory")
                .setParameter("category", id_category)
                .getSingleResult();
        return count;
    }

    /**
     * counts active products (sorted) filtered by name, category
     */
    public long countActiveByNameCategory(String name, Category id_category) {
        long count = (Long) em.createNamedQuery("Product.countActiveByNameCategory")
                .setParameter("name", "%" + name.replaceAll("%", "^%") + "%")
                .setParameter("category", id_category)
                .getSingleResult();
        return count;
    }
    
    
    /**
     * finds all products (sorted)
     */
    public List<Product> findAll(int firstResult, int maxResults, String sortBy, boolean isAsc) {
        CriteriaBuilder cb = em.getCriteriaBuilder();

        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);
        query.select(product);
       
        if (isAsc) {
            query.orderBy(cb.asc(product.get(sortBy)));
        } else {
            query.orderBy(cb.desc(product.get(sortBy)));
        }

        return em.createQuery(query).setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();

    }

    /**
     * finds all products (sorted) filtered by name
     */
    public List<Product> findAll(int firstResult,
            int maxResults,
            String sortBy,
            boolean isAsc,
            String productName) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);

        ParameterExpression<Integer> status = cb.parameter(Integer.class);
        ParameterExpression<String> name = cb.parameter(String.class);
        Expression<String> namePath = product.get("name");

        query.select(product);
        query.where(cb.like(cb.lower(namePath), cb.lower(name),'^'));
        if (isAsc) {
            query.orderBy(cb.asc(product.get(sortBy)));
        } else {
            query.orderBy(cb.desc(product.get(sortBy)));
        }

        return em.createQuery(query).setParameter(name, "%" + productName.replaceAll("%","^%") + "%")
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();
    }

    /**
     * finds all products (sorted)filtered by category
     */
    public List<Product> findAll(int firstResult,
            int maxResults,
            String sortBy,
            boolean isAsc,
            Category id_category) {

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);
        query.select(product);

        ParameterExpression<Category> category = cb.parameter(Category.class);


        query.where(cb.equal(product.get("category"), category));
        if (isAsc) {
            query.orderBy(cb.asc(product.get(sortBy)));
        } else {
            query.orderBy(cb.desc(product.get(sortBy)));
        }

        return em.createQuery(query)
                .setParameter(category, id_category)
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();

    }

    /**
     * finds all products (sorted) filtered by name, category
     */
    public List<Product> findAll(int firstResult,
            int maxResults,
            String sortBy,
            boolean isAsc,
            String productName,
            Category id_category) {

        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Product> query = cb.createQuery(Product.class);
        Root<Product> product = query.from(Product.class);

      
        ParameterExpression<String> name = cb.parameter(String.class);
        ParameterExpression<Category> category = cb.parameter(Category.class);
        Expression<String> namePath = product.get("name");
        

        query.select(product);
        query.where(cb.like(cb.lower(namePath), cb.lower(name),'^'),
                cb.equal(product.get("category"), category));
        if (isAsc) {
            query.orderBy(cb.asc(product.get(sortBy)));
        } else {
            query.orderBy(cb.desc(product.get(sortBy)));
        }

        return em.createQuery(query)
                .setParameter(name, "%" + productName.replaceAll("%","^%") + "%")
                .setParameter(category, id_category)
                .setFirstResult(firstResult)
                .setMaxResults(maxResults)
                .getResultList();

    }

    /**
     * counts all products (sorted)
     */
    public long countAll() {
        long count = (Long) em.createNamedQuery("Product.countAll")
                .getSingleResult();
        return count;
    }

    /**
     * counts all products (sorted) filtered by name
     */
    public long countAllByName(String name) {
        long count = (Long) em.createNamedQuery("Product.countAllByName")
                .setParameter("name", "%" + name.replaceAll("%", "^%") + "%")
                .getSingleResult();
        return count;
    }

    /**
     * counts all products (sorted)filtered by category
     */
    public long countAllByCategory(Category id_category) {
        long count = (Long) em.createNamedQuery("Product.countAllByCategory")
                .setParameter("category", id_category)
                .getSingleResult();
        return count;
    }

    /**
     * counts all products (sorted) filtered by name, category
     */
    public long countAllByNameCategory(String name, Category id_category) {
        long count = (Long) em.createNamedQuery("Product.countAllByNameCategory")
                .setParameter("name", "%" + name.replaceAll("%", "^%") + "%")
                .setParameter("category", id_category)
                .getSingleResult();
        return count;
    }
    
    
    
    
    
    
    
    
}
